package com.geping.etl.common.repository;

import java.util.List;

import org.omg.CORBA.PUBLIC_MEMBER;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.geping.etl.common.entity.Sys_User;

@Repository
public interface Sys_UserRepository extends PagingAndSortingRepository<Sys_User, Integer>,JpaSpecificationExecutor<Sys_User>{
	//根据id查询用户
	@Query("select s from Sys_User s where s.id = ?1")
	public Sys_User findUserById(Integer id);
	
	//查询所有未删除的用户
	@Query("select s from Sys_User s where s.isDelete = 'N'")
	public List<Sys_User> findAllSysUser();
	
	//根据登录账号查询用户，避免新增用户时系统中的登录账号重复
	@Query("select s from Sys_User s where s.loginId = ?1 and s.isDelete = 'N'")
	public Sys_User getOneByLoginId(String loginId);
	
	//登录验证    获取到登录账号 和 密码  (未锁定和未删除的用户才能登录，即isDelete = 'N' isLocked = 'N')
	@Query("select s from Sys_User s where s.loginId = ?1 and s.password = ?2 and s.isLocked = 'N' and s.isDelete = 'N'")
	public Sys_User getOne(String loginId,String password);
		
	//申请修改
	@Modifying 
	@Query("update Sys_User u set u.description = ?2,u.handlename = ?3,u.handleperson = ?4,u.handledate = ?5 where u.id = ?1")
	public int applyEdit(Integer id,String desc,String handlename,String handleperson,String handledate);
	
	//重置密码
	@Modifying 
	@Query("update Sys_User u set u.password = ?1 where u.loginId = ?2")
	public int updatePassword(String password, String loginId);
	
	//申请重置密码
	@Modifying 
	@Query("update Sys_User u set u.password = ?2,u.handlename = ?3,u.handleperson = ?4,u.handledate = ?5 where u.id = ?1")
	public int applyReaptPwd(Integer id,String pwd,String handlename,String handleperson,String handledate);
	
	//修改登录时的登录时间和是否为首次登录
	@Modifying 
	@Query("update Sys_User u set u.lastLoginDate = ?1,u.isFirstLogin = ?2 where u.id = ?3")
	public int updateSomeSysUser(String lastLoginDate,String isFirstLogin,Integer id);
	
	//修改登录时的登录时间
	@Modifying 
	@Query("update Sys_User u set u.lastLoginDate = ?1 where u.id = ?2")
	public int updateLastLoginDate(String lastLoginDate,Integer id);
	
	//获取最大ID数
	@Query("select max(s.id) from Sys_User s")
	public Integer findMaxId();
	
	//根据用户名模糊查询用户信息
	@Query("select s from Sys_User s where s.isDelete = 'N' and s.userCname like CONCAT('%',?1,'%')")
	public List<Sys_User> getSys_UserByLikeUserCname(String userCname);
	
	//修改用户信息(不包括修改密码)
	@Modifying
	@Query("update Sys_User s set s.handlename = ' ',s.loginId=?2,s.userEname=?3,s.userCname=?4,s.orgId=?5,s.departId=?6,s.tel=?7,s.mobile=?8,s.address=?9,s.email=?10,s.ip=?11,s.isDept=?12,s.startDate = '1' where s.id = ?1")
	public int updateSysUser(Integer id_edit,String loginId_edit,String userEname_edit,String userCname_edit,String orgId_edit,String departId_edit,String tel_edit,String mobile_edit,String address_edit,String email_edit,String ip_edit,String is_dept);

	@Modifying
	@Query("update Sys_User s set s.handlename = ' ',s.loginId=?2,s.userEname=?3,s.userCname=?4,s.departId=?5,s.tel=?6,s.mobile=?7,s.address=?8,s.email=?9,s.ip=?10,s.isDept=?11,s.startDate = '1',s.endDate = ?12 where s.id = ?1")
	public int updateSysUser2(Integer id_edit,String loginId_edit,String userEname_edit,String userCname_edit,String departId_edit,String tel_edit,String mobile_edit,String address_edit,String email_edit,String ip_edit,String is_dept, String orgStr);


	@Modifying
	@Query("update Sys_User s set s.handlename = ' ',s.loginId=?2,s.userEname=?3,s.userCname=?4,s.departId=?5,s.tel=?6,s.mobile=?7,s.address=?8,s.email=?9,s.ip=?10,s.isDept=?11,s.startDate = '1',s.endDate = ?12, s.systemDept = ?13  where s.id = ?1")
	public int updateSysUserDept(Integer id_edit,String loginId_edit,String userEname_edit,String userCname_edit,String departId_edit,String tel_edit,String mobile_edit,String address_edit,String email_edit,String ip_edit,String is_dept, String orgStr,String listStrdept);

	//修改用户密码
	@Modifying
	@Query("update Sys_User u set u.password = ?2 ,u.lastModifyDate = ?3,u.pwdstr = ?4 where u.id = ?1")
	public int editPasswordSys_User(Integer editPasswordId,String password_edit,String date,String str);
	
	//修改用户是否锁定
	@Modifying
	@Query("update Sys_User s set s.isLocked = ?2 where s.id = ?1")
	public int editIsLockedSys_User(Integer id,String isLocked);
	
	//锁定用户并填写锁定原因
	@Modifying
	@Query("update Sys_User s set s.isLocked = ?2,s.userLockedReson = ?3 where s.id = ?1")
	public int lockedUser(Integer id,String isLocked,String reason);
	
	//逻辑删除用户
	@Modifying
	@Query("update Sys_User s set s.isDelete = 'Y' where s.id = ?1")
	public int deleteSys_User(Integer id);
	
	//通过登录用户的id查询该用户所有的角色资源信息
	@Query(value="select sarr.ID as id,sarr.SUBJECT_ID as subjectId,sarr.ROLE_ID as roleId,sarr.RES_TYPE as resType,sarr.RES_ID as resId,sarr.RES_VALUE as resValue,sarr.RES_VALUE_NAME as resValueName "
			+ "   from sys_auth_role_resource sarr "
			+ "   WHERE sarr.ROLE_ID in"
			+ "   (select saru.ROLE_ID from sys_auth_role_user saru where saru.USER_ID = ?)",nativeQuery=true)
	public List getAllRoleResource(String userId);
	
	//根据用户名查找用户
	@Query("select s.loginId from Sys_User s where s.loginId = ?1")
	public String findLoginIdSys_User(String loginId);
	
	@Query("select s.loginId from Sys_User s where s.id = ?1 and s.isLocked = 'N'")
	public String findSys_UserById(Integer id);
	
	//申请解锁
	@Modifying 
	@Query("update Sys_User u set u.handlename = ?2,u.handleperson = ?3,u.handledate = ?4 where u.id = ?1")
	public int applyNoLock(Integer id,String handlename,String handleperson,String handledate);
	
	//申请锁定
	@Modifying 
	@Query("update Sys_User u set u.handlename = ?2,u.handleperson = ?3,u.handledate = ?4,u.userLockedReson = ?5 where u.id = ?1")
	public int applyDoLock(Integer id,String handlename,String handleperson,String handledate,String reason);
	
	//同意新增/修改
	@Modifying
	@Query("update Sys_User u set u.handlename = ?2 where u.id = ?1")
	public int agreenAddOrEdit(Integer id,String handlename);
	
	//同意重置密码
	@Modifying 
	@Query("update Sys_User u set u.handlename = ' ' where u.id = ?1")
	public int agreenReapt(Integer id);

	//增加登陆失败次数
	@Modifying
	@Query("update Sys_User u set u.failNums = ?1 where u.loginId =?2")
	public void updateTime(String time,String loginId);

	//增加登陆失败次数
	@Modifying
	@Query("update Sys_User u set u.failNums = ?1 where u.id =?2")
	public void updateTimeById(String time,Integer id);
	
	//同意锁定
	@Transactional
	@Modifying 
	@Query("update Sys_User u set u.handlename = ' ',u.isLocked = ?2 where u.id = ?1")
	public int agreenLock(Integer id,String lock);
	
	//同意解锁
	@Modifying 
	@Query("update Sys_User u set u.handlename = ' ',u.isLocked = ?2,u.userLockedReson = '' where u.id = ?1")
	public int agreenNoLock(Integer id,String lock);
	
	//拒绝新增/修改
	@Modifying 
	@Query("update Sys_User u set u.handlename = ' ',u.description = '' where u.id = ?1")
	public int refuseAddOrEdit(Integer id);
	
	//拒绝重置密码/锁定/解锁
	@Modifying 
	@Query("update Sys_User u set u.handlename = ' ',u.userLockedReson = '' where u.id = ?1")
	public int refuseSomeHandle(Integer id);

	@Modifying
	@Query("update Sys_User u set u.isFirstLogin =?2 where u.id = ?1")
	void updateFirstLoginStatus(Integer id,String status);

	@Modifying
	@Query("update Sys_User u set u.departId =?2 where u.departId = ?1")
	void updateDeptIdByOldDeptId(String oldDeptId, String newDeptId);

	@Modifying
	@Query("update Sys_User s set s.isDelete = 'Y' where s.departId = ?1")
	int deleteSys_UserByDeptId(String deptId);
}
